<?php

class DB_model extends CI_Model
{
    private $tableName; //表名称
    private $where; //查询的where条件
    private $orderBy; //排序的条件
    private $field; //查询的字段
    private $select;
    private $sql; //执行的sql语句
    private $join; //连接查询语句
    private $groupBy; //分组查询
    private $limit; //设置查询条数

    public function __construct($table_name = '')
    {
        $this->tableName = $table_name;
        $this->join = array();
        $this->field = "*";
    }

    /*
		设置表名称
		params
			table_name 表名称
    */
    public function setTableName($table_name = '')
    {
    	$this->tableName = $table_name;
    }

    /*
        查询的数据表的字段
        params
            field 查询字段
    */
    public function field($field = '')
    {
        if(empty($field) == false){
            $this->field = is_array($field) ? implode(", ", $field) : $field;
        }


        return $this;
    }

    /*
        连接查询
        params
            join 连接查询的字符串
    */
    public function join($join = "")
    {
        if(empty($join) == false){
            $this->join[] = $join;
        }

        return $this;
    }

    /*
        查询的数据表的条件
        params
            where 查询条件的字符串
    */
    public function where($where = '')
    {
        if(empty($where) == false){
            $this->where = $where;
        }
        return $this;
    }

    /*
        设置order排序
        params
            order 排序的字符串
    */
    public function order($order = '')
    {
        if(empty($order) == false){
            $this->orderBy = $order;
        }

        return $this;
    }

    /*
        设置group查询
        params
            group group的字符串
    */
    public function group($group = '')
    {
        if(empty($group) == false){
            $this->groupBy = $group;
        }

        return $this;
    }

    /*
        设置查询条数
        params
            limit 查询条数字符串
    */
    public function limit($limit = '')
    {
        if(empty($limit) == false){
            $this->limit = $limit;
        }
        return $this;
    }

    /*
        执行查询单条记录
        params
            run 是否执行 boolean 
    */
    public function find($run = true)
    {
        if($run){
            $this->limit("1");

            $this->sql = $this->createSelectSqlStr();
            $res = $this->query($this->sql);

            return is_array($res) ? $res[0] : false;
        }
    }
    /*
        执行查询多条记录
        params
            run 是否执行 boolean 
    */
    public function select($run = true)
    {
        if($run){
            $this->sql = $this->createSelectSqlStr();

            return $this->query($this->sql);
        }
    }

    /*
        添加数据
        params
            data 需要插入数据的数组对象
            replace 替换选项
    */
    public function add($data = array(), $replace = false)
    {
        if(empty($data) == false){
            $this->sql = $this->createInsertSql($data, $replace);

            return $this->insert($this->sql);
        }
    }

    /*
        批量添加数据
        params
            data 需要插入数据的二维数组
            replace 替换选项
    */
    public function addAll($data = array(), $replace = false)
    {
        if(empty($data) == false){
            $this->sql = $this->createInsertAllSql($data, $replace);

            return $this->insert($this->sql);
        }
    }

    /*
        更新数据
        params
            data 需要更新的数据对象
    */
    public function save($data = array())
    {
        if(empty($data) == false && empty($this->where) == false){
            $this->sql = $this->createUpdateSql($data);

            return $this->update($this->sql);
        }else{
            return false;
        }
    }

    /*
        统计数量
    */ 
    public function count()
    {
        $this->sql = $this->createCountSqlStr();

        $res = $this->query($this->sql);

        return is_numeric($res[0]['cnt']) ? $res[0]['cnt'] : false;
    }

    /*
        创建select查询语句
    */
    public function createSelectSqlStr()
    {
        $sql = sprintf("select %s from %s", $this->field, $this->tableName);

        if(empty($this->join) == false){
            $sql .= " ". implode(" ", $this->join);
        }

        if(empty($this->where) == false){
            $sql .= sprintf(" where %s ", $this->where);
        }

        if(empty($this->groupBy) == false){
            $sql .= sprintf(" group by %s", $this->groupBy);
        }

        if(empty($this->orderBy) == false){
            $sql .= sprintf(" order by %s", $this->orderBy);
        }

        if(empty($this->limit) == false){
            $sql .= " limit ". $this->limit;
        }

        return $sql;
    }

    /*
        创建count查询语句
    */
    public function createCountSqlStr()
    {
        $sql = sprintf("select count(*) cnt from %s", $this->tableName);

        if(empty($this->join) == false){
            $sql .= " ". implode(" ", $this->join);
        }

        if(empty($this->where) == false){
            $sql .= sprintf(" where %s ", $this->where);
        }

        return $sql;
    }

    /*
        创建插入数据语句
        params
            data 需要插入数据的数组对象
            replace 替换选项
    */
    public function createInsertSql($data = array(), $replace = false)
    {
        $field = implode(", ", array_keys($data));

        $insert = $replace ? "replace" : "insert";

        $insertData = $this->createInsertData($data);

        $sql = sprintf("%s into %s (%s) values%s", $insert, $this->tableName, $field, $insertData);

        return $sql;

    }

    /*
        创建插入数据语句
        params
            data 需要插入数据的数组对象
            replace 替换选项
    */
    public function createInsertAllSql($data = array(), $replace = false)
    {
        $field = implode(", ", array_keys($data[0]));

        $insert = $replace ? "replace" : "insert";

        $insertData = $this->createInsertAllData($data);


        $sql = sprintf("%s into %s (%s) values%s", $insert, $this->tableName, $field, $insertData);

        return $sql;

    }

    /*
        生成insert的值
        params
            data 需要更新的数据对象
    */
    public function createInsertData($data = array())
    {
        $res = array();

        foreach ($data as $key => $value) {
            $res[] = is_numeric($value) ? $value : sprintf("'%s'", $value);
        }

        $res = sprintf("(%s)", implode(", ", $res));
        return $res;
    }

    /*

    */
    public function createInsertAllData($data = array())
    {
        $res = array();

        foreach ($data as $key => $value) {
            $res[] = $this->createInsertData($value);
        }

        return implode(", ", $res);
    }

    /*
        创建更新sql语句
        params
            data 需要更新的数据对象
    */
    public function createUpdateSql($data = array())
    {
        $updateData = $this->createUpdateData($data);

        $sql = sprintf("update %s set %s where %s", $this->tableName, $updateData, $this->where);

        return $sql;
    }

    /*
        生成update的值
        params
            data 更新的数组对象
    */
    public function createUpdateData($data = array())
    {
        $res = array();

        foreach ($data as $key => $value) {
            $value = is_numeric($value) ? $value : sprintf("'%s'", $value);
            $res[] = sprintf('%s=%s', $key, $value);
        }

        return implode(", ", $res);
    }

    /*
        执行插入语句
        params
            sql 执行的sql语句
    */
    public function insert($sql = '')
    {
        global $cur_user_id;
        $res = DB::insert_db($cur_user_id, $sql, 0, 1);

        return $res;
    }

    /*
        执行更新数据
        params
            sql
    */
    public function update($sql = "")
    {
        global $cur_user_id;
        $res = DB::update_db($cur_user_id, $sql, 0, 1);

        return $res;
    }

    /*
        删除数据
    */
    public function delete()
    {
        if(empty($this->where) == false){
            $this->sql = sprintf("delete from %s where %s", $this->tableName, $this->where);

            return $this->exec_delete($this->sql);
        }else{
            return false;
        }
    }

    /*
        执行删除语句
        params
            sql 执行的sql语句
    */
    public function exec_delete($sql = '')
    {
        global $cur_user_id;
        $res = DB::delete_db($cur_user_id, $sql, 0, '', 0);

        return $res;
    }

    /*
        执行查询语句
        params
            sql 执行的sql语句
    */
    public function query($sql = '')
    {
        global $cur_user_id;
        $res = DB::select_db($cur_user_id, $sql, 0, 1);

        foreach ($res as $key => $value) {
            foreach ($value as $key_1 => $value_1) {
                if(is_numeric($key_1)){
                    unset($res[$key][$key_1]);
                }
            }
        }
        return $res;
    }

    /*
        执行更新语句
        params
            sql 执行的sql语句
    */
    public function execute($sql = '')
    {
        global $cur_user_id;
        $res = DB::update_db2($cur_user_id, $sql, 0, 1);

        return $res;
    }

    /*
        获取最后一条sql
    */
    public function getLastSql()
    {
        return $this->sql;
    }

    /*
        获取表名称
    */
    public function getTableName()
    {
        return $this->tableName;
    }

    /*
        开始事务
    */
    public function startTrans()
    {
        mysql_query("BEGIN");
    }

    /*
        事务回滚
    */
    public function rollback()
    {
        mysql_query("ROLLBACK");
        mysql_query("END");
    }

    /*
        事务提交
    */
    public function commit()
    {
        mysql_query("COMMIT");
        mysql_query("END");
    }
}